{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 2.1 从附件 2 中筛选出复混肥料的产品，将所有复混肥料按照总无机养分百分比的取值等距分为 10 组。根据每个产品所在的分组，为其打上分组标签（标签用 1~10 表示），将完整的结果保存到文件“result2_1.xlsx”中。分析复混肥料产品的分布特点，在报告中绘制产品登记数量的直方图，给出处理思路及过程，并按登记数量从大到小列出登记数量最大的前 3 个分组及相应的产品登记数量。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 驱动器 C 中的卷是 OS\n",
      " 卷的序列号是 AE6F-00B3\n",
      "\n",
      " C:\\Users\\27420\\PycharmProjects\\训练\\gitee\\future-teddy\\teddy-B\\code\\data 的目录\n",
      "\n",
      "2021/11/14  10:44    <DIR>          .\n",
      "2021/11/14  10:44    <DIR>          ..\n",
      "2021/11/14  10:11           174,660 result1_1.xlsx\n",
      "2021/11/14  10:19           184,712 result1_2.xlsx\n",
      "2021/11/14  09:32           410,222 result2_1.xlsx\n",
      "2021/11/14  09:46            93,338 result2_2.xlsx\n",
      "2021/11/14  10:45            82,322 result3_2.xlsx\n",
      "2021/11/02  20:09           196,721 附件1.xlsx\n",
      "2021/11/02  20:09           575,770 附件2.xlsx\n",
      "2021/11/02  20:10            40,230 附件3.xlsx\n",
      "2021/11/02  20:11            22,801 附件4.xlsx\n",
      "               9 个文件      1,780,776 字节\n",
      "               2 个目录 111,129,718,784 可用字节\n"
     ]
    }
   ],
   "source": [
    "ls data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导包\n",
    "\n",
    "import array\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import jieba\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "matplotlib.rcParams['font.sans-serif'] = ['SimHei'] \n",
    "matplotlib.rcParams['font.family']='sans-serif'\n",
    "matplotlib.rcParams['axes.unicode_minus'] = False "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_excel('data/附件2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>武汉楚天艾科生物工程有限公司</td>\n",
       "      <td>有机-无机复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.00</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.2</td>\n",
       "      <td>鄂农肥（2009）准字0001号</td>\n",
       "      <td>2014-02-25</td>\n",
       "      <td>2019-02</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.07</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0004号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.15</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0005号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.10</td>\n",
       "      <td>中氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0006号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>湖北澳特尔化工有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.15</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字00079号</td>\n",
       "      <td>2014-10-25</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.45</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号            企业名称     产品通用名称 产品形态  总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "0   1  武汉楚天艾科生物工程有限公司  有机-无机复混肥料   粒状   0.09     0.06    0.00   无氯     0.2   \n",
       "1   2   嘉施利（应城）化肥有限公司       复混肥料   粒状   0.17     0.17    0.07   低氯     0.0   \n",
       "2   3   嘉施利（应城）化肥有限公司       复混肥料   粒状   0.20     0.05    0.15   无氯     0.0   \n",
       "3   4   嘉施利（应城）化肥有限公司       复混肥料   粒状   0.26     0.08    0.10   中氯     0.0   \n",
       "4   5     湖北澳特尔化工有限公司       复混肥料   粒状   0.15     0.15    0.15   无氯     0.0   \n",
       "\n",
       "              正式登记证号        发证日期      有效期 产品商品名称 适用作物  总无机养分百分比  \n",
       "0   鄂农肥（2009）准字0001号  2014-02-25  2019-02    NaN  NaN      0.15  \n",
       "1   鄂农肥（2009）准字0004号  2014-08-15  2019-08    NaN  NaN      0.41  \n",
       "2   鄂农肥（2009）准字0005号  2014-08-15  2019-08    NaN  NaN      0.40  \n",
       "3   鄂农肥（2009）准字0006号  2014-08-15  2019-08    NaN  NaN      0.44  \n",
       "4  鄂农肥（2009）准字00079号  2014-10-25  2019-10    NaN  NaN      0.45  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 提取复混肥料的数据\n",
    "data_1 = data[data['产品通用名称']=='复混肥料']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.72\n",
      "0.0\n"
     ]
    }
   ],
   "source": [
    "#找出总无机养分百分比的最大值和最小值\n",
    "print(data_1['总无机养分百分比'].max())\n",
    "print(data_1['总无机养分百分比'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#0.72-0.0 等距分为十组 分隔区间为\n",
    "#[0.0-0.072] [0.072-0.144] [0.144-0.216] [0.216-0.288] [0.288-0.36] [0.36-0.432] [0.432-0.504] [0.504-0.576] [0.576-0.648] [0.648-0.72]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    }
   ],
   "source": [
    "step = (data_1.总无机养分百分比.max() - data_1.总无机养分百分比.min())/10\n",
    "bins = [data_1.总无机养分百分比.min() + i * step for i in range(11)]\n",
    "data_1['标签'] = pd.cut(data_1['总无机养分百分比'], bins, labels=['1','2','3','4','5','6','7','8','9','10'])\n",
    "# 因为区间是左闭右开，使用还要将 总无机养分百分比为0.0的标签改为1\n",
    "data_1.loc[data_1['总无机养分百分比']==0.0,'标签']='1'\n",
    "data_1.loc[data_1['总无机养分百分比']==0.72,'标签']='10'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 保存\n",
    "data_1.to_excel('data/result2_1.xlsx')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 2.2   从附件 2 中筛选出有机肥料的产品，将产品按照总无机养分百分比和有机质百分比分别等距分为 10 组，并为每个产品打上分组标签 (1,1), (1,2),⋯, (10,10)，将完整的结果保存到文件“result2_2.xlsx”中。请在报告中给出处理思路及过程，并根据分组情况绘制有机肥料产品的分布热力图，其中横轴代表总无机养分分组，纵轴代表有机质分组。在此基础上，分析有机肥料产品的分布特点，并按登记数量从大到小列出登记数量最大的前 3 个分组及相应的产品登记数量。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>231</td>\n",
       "      <td>湖北中化东方肥料有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.60</td>\n",
       "      <td>鄂农肥（2009）准字0348号</td>\n",
       "      <td>2015-01-20</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>320</td>\n",
       "      <td>武汉市沃农肥业有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字0595号</td>\n",
       "      <td>2015-01-20</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>425</td>\n",
       "      <td>湖北太阳雨三农科技有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字0915号</td>\n",
       "      <td>2015/11/10</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>474</td>\n",
       "      <td>武汉裕龙生物科技有限责任公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字1116号</td>\n",
       "      <td>2015/11/20</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>539</td>\n",
       "      <td>湖北地利奥生物科技有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2011）准字0038号</td>\n",
       "      <td>2016/03/22</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号            企业名称 产品通用名称 产品形态   总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "230  231    湖北中化东方肥料有限公司   有机肥料   粉状  0.0267   0.0267  0.0267   无氯    0.60   \n",
       "319  320     武汉市沃农肥业有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "424  425   湖北太阳雨三农科技有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "473  474  武汉裕龙生物科技有限责任公司   有机肥料   粒状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "538  539   湖北地利奥生物科技有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "\n",
       "               正式登记证号        发证日期      有效期 产品商品名称 适用作物  总无机养分百分比  \n",
       "230  鄂农肥（2009）准字0348号  2015-01-20  2020-01    NaN  NaN    0.0801  \n",
       "319  鄂农肥（2010）准字0595号  2015-01-20  2020-01    NaN  NaN    0.0501  \n",
       "424  鄂农肥（2010）准字0915号  2015/11/10  2020-11    NaN  NaN    0.0501  \n",
       "473  鄂农肥（2010）准字1116号  2015/11/20  2020-11    NaN  NaN    0.0501  \n",
       "538  鄂农肥（2011）准字0038号  2016/03/22  2021-03    NaN  NaN    0.0501  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 筛选出有机肥料的产品\n",
    "data_2 = data[data['产品通用名称']=='有机肥料']\n",
    "data_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.9\n",
      "0.0\n",
      "0.43\n",
      "0.0501\n"
     ]
    }
   ],
   "source": [
    "# 查找有机质百分比和总无机养分百分比的最值\n",
    "print(data_2['有机质百分比'].max())\n",
    "print(data_2['有机质百分比'].min())\n",
    "\n",
    "print(data_2['总无机养分百分比'].max())\n",
    "print(data_2['总无机养分百分比'].min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:10: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  # Remove the CWD from sys.path while we load stuff.\n"
     ]
    }
   ],
   "source": [
    "#等距分为十组  赋上标签\n",
    "step_1 = (data_2.有机质百分比.max() - data_2.有机质百分比.min())/10\n",
    "bins_1 = [data_2.有机质百分比.min() + i * step_1 for i in range(11)]\n",
    "data_2['标签_1'] = pd.cut(data_2['有机质百分比'], bins_1, labels=['1','2','3','4','5','6','7','8','9','10'])\n",
    "#处理有机质百分比为0的值\n",
    "data_2.loc[data_2['有机质百分比']==0,'标签_1']='1'\n",
    "data_2.loc[data_2['有机质百分比']==0.9,'标签_1']='10'\n",
    "step_2 = (data_2.总无机养分百分比.max() - data_2.总无机养分百分比.min())/10\n",
    "bins_2 = [data_2.总无机养分百分比.min() + i * step_2 for i in range(11)]\n",
    "data_2['标签_2'] = pd.cut(data_2['总无机养分百分比'], bins_2, labels=['1','2','3','4','5','6','7','8','9','10'])\n",
    "#处理总无机养分百分比为0.0501的值\n",
    "data_2.loc[data_2['总无机养分百分比']==0.0501,'标签_2']='1'\n",
    "data_2.loc[data_2['总无机养分百分比']==0.43,'标签_2']='10'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:543: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self.obj[item] = s\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "      <th>标签_1</th>\n",
       "      <th>标签_2</th>\n",
       "      <th>标签</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>231</td>\n",
       "      <td>湖北中化东方肥料有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>0.0267</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.60</td>\n",
       "      <td>鄂农肥（2009）准字0348号</td>\n",
       "      <td>2015-01-20</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0801</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>7,1,7,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>319</th>\n",
       "      <td>320</td>\n",
       "      <td>武汉市沃农肥业有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字0595号</td>\n",
       "      <td>2015-01-20</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6,1,6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>425</td>\n",
       "      <td>湖北太阳雨三农科技有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字0915号</td>\n",
       "      <td>2015/11/10</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6,1,6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>473</th>\n",
       "      <td>474</td>\n",
       "      <td>武汉裕龙生物科技有限责任公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2010）准字1116号</td>\n",
       "      <td>2015/11/20</td>\n",
       "      <td>2020-11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6,1,6,1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>539</td>\n",
       "      <td>湖北地利奥生物科技有限公司</td>\n",
       "      <td>有机肥料</td>\n",
       "      <td>粉状</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>0.0167</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.45</td>\n",
       "      <td>鄂农肥（2011）准字0038号</td>\n",
       "      <td>2016/03/22</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0501</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6,1,6,1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      序号            企业名称 产品通用名称 产品形态   总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "230  231    湖北中化东方肥料有限公司   有机肥料   粉状  0.0267   0.0267  0.0267   无氯    0.60   \n",
       "319  320     武汉市沃农肥业有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "424  425   湖北太阳雨三农科技有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "473  474  武汉裕龙生物科技有限责任公司   有机肥料   粒状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "538  539   湖北地利奥生物科技有限公司   有机肥料   粉状  0.0167   0.0167  0.0167   无氯    0.45   \n",
       "\n",
       "               正式登记证号        发证日期      有效期 产品商品名称 适用作物  总无机养分百分比 标签_1 标签_2  \\\n",
       "230  鄂农肥（2009）准字0348号  2015-01-20  2020-01    NaN  NaN    0.0801    7    1   \n",
       "319  鄂农肥（2010）准字0595号  2015-01-20  2020-01    NaN  NaN    0.0501    6    1   \n",
       "424  鄂农肥（2010）准字0915号  2015/11/10  2020-11    NaN  NaN    0.0501    6    1   \n",
       "473  鄂农肥（2010）准字1116号  2015/11/20  2020-11    NaN  NaN    0.0501    6    1   \n",
       "538  鄂农肥（2011）准字0038号  2016/03/22  2021-03    NaN  NaN    0.0501    6    1   \n",
       "\n",
       "          标签  \n",
       "230  7,1,7,1  \n",
       "319  6,1,6,1  \n",
       "424  6,1,6,1  \n",
       "473  6,1,6,1  \n",
       "538  6,1,6,1  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_2['标签'] = data_2[data_2.columns[15:]].apply(lambda x: ','.join(x.dropna()),axis=1) \n",
    "data_2.loc[data_2['标签']=='1','标签']='1,1'\n",
    "data_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_2.to_excel('data/result2_2.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 2.3   从附件 2 中筛选出复混肥料的产品，按照氮、磷、钾养分的百分比，使用聚类算法将这些产品分为 4 类。根据聚类结果为每个产品打上聚类标签（标签用 1~4 表示），并将完整的结果保存到文件“result2_3.xlsx”中。请在报告中给出处理思路及过程，根据聚类标签绘制肥料产品的三维散点图和散点图矩阵，并通过绘制聚类结果的雷达图分析每个聚类的特征。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((7619, 15), (5954, 15))"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data2 = pd.read_excel(\"data/附件2.xlsx\")\n",
    "data = data2[data2['产品通用名称']=='复混肥料']\n",
    "data2.shape,data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.42857142857142855"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "P_proportion = (30*2)/((30*2)+(16*5))\n",
    "P_proportion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8297872340425532"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "K_proportion = (39*2)/((39*2)+16)\n",
    "K_proportion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "data['总磷百分比'] = data['P2O5百分比']*P_proportion\n",
    "data['总钾百分比'] = data['K2O百分比']*K_proportion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "      <th>总磷百分比</th>\n",
       "      <th>总钾百分比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.07</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0004号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.072857</td>\n",
       "      <td>0.058085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.15</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0005号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.021429</td>\n",
       "      <td>0.124468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.10</td>\n",
       "      <td>中氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0006号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.034286</td>\n",
       "      <td>0.082979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>湖北澳特尔化工有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.15</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字00079号</td>\n",
       "      <td>2014-10-25</td>\n",
       "      <td>2019-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.064286</td>\n",
       "      <td>0.124468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.11</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0007号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.021429</td>\n",
       "      <td>0.091277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号           企业名称 产品通用名称 产品形态  总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "1   2  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.17     0.17    0.07   低氯     0.0   \n",
       "2   3  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.20     0.05    0.15   无氯     0.0   \n",
       "3   4  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.26     0.08    0.10   中氯     0.0   \n",
       "4   5    湖北澳特尔化工有限公司   复混肥料   粒状   0.15     0.15    0.15   无氯     0.0   \n",
       "5   6  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.20     0.05    0.11   无氯     0.0   \n",
       "\n",
       "              正式登记证号        发证日期      有效期 产品商品名称 适用作物  总无机养分百分比     总磷百分比  \\\n",
       "1   鄂农肥（2009）准字0004号  2014-08-15  2019-08    NaN  NaN      0.41  0.072857   \n",
       "2   鄂农肥（2009）准字0005号  2014-08-15  2019-08    NaN  NaN      0.40  0.021429   \n",
       "3   鄂农肥（2009）准字0006号  2014-08-15  2019-08    NaN  NaN      0.44  0.034286   \n",
       "4  鄂农肥（2009）准字00079号  2014-10-25  2019-10    NaN  NaN      0.45  0.064286   \n",
       "5   鄂农肥（2009）准字0007号  2014-08-15  2019-08    NaN  NaN      0.36  0.021429   \n",
       "\n",
       "      总钾百分比  \n",
       "1  0.058085  \n",
       "2  0.124468  \n",
       "3  0.082979  \n",
       "4  0.124468  \n",
       "5  0.091277  "
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>总磷百分比</th>\n",
       "      <th>总钾百分比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.072857</td>\n",
       "      <td>0.058085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.20</td>\n",
       "      <td>0.021429</td>\n",
       "      <td>0.124468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.26</td>\n",
       "      <td>0.034286</td>\n",
       "      <td>0.082979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.15</td>\n",
       "      <td>0.064286</td>\n",
       "      <td>0.124468</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.20</td>\n",
       "      <td>0.021429</td>\n",
       "      <td>0.091277</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   总氮百分比     总磷百分比     总钾百分比\n",
       "1   0.17  0.072857  0.058085\n",
       "2   0.20  0.021429  0.124468\n",
       "3   0.26  0.034286  0.082979\n",
       "4   0.15  0.064286  0.124468\n",
       "5   0.20  0.021429  0.091277"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_data = data[['总氮百分比','总磷百分比','总钾百分比']]\n",
    "new_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "KMeans(n_clusters=4)"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将肥料分为4类\n",
    "from sklearn.cluster import KMeans\n",
    "km = KMeans(n_clusters=4)\n",
    "km.fit(new_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2, 3, 0, ..., 1, 2, 2])"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "predict = km.predict(new_data)\n",
    "predict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>产品商品名称</th>\n",
       "      <th>适用作物</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "      <th>聚类标签</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.07</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0004号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.41</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>嘉施利（应城）化肥有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>粒状</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.15</td>\n",
       "      <td>无氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>鄂农肥（2009）准字0005号</td>\n",
       "      <td>2014-08-15</td>\n",
       "      <td>2019-08</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.40</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号           企业名称 产品通用名称 产品形态  总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "1   2  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.17     0.17    0.07   低氯     0.0   \n",
       "2   3  嘉施利（应城）化肥有限公司   复混肥料   粒状   0.20     0.05    0.15   无氯     0.0   \n",
       "\n",
       "             正式登记证号        发证日期      有效期 产品商品名称 适用作物  总无机养分百分比  聚类标签  \n",
       "1  鄂农肥（2009）准字0004号  2014-08-15  2019-08    NaN  NaN      0.41     3  \n",
       "2  鄂农肥（2009）准字0005号  2014-08-15  2019-08    NaN  NaN      0.40     4  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['聚类标签'] = predict+1\n",
    "data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel(\"data/result2_3.xlsx\",index=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
